-- Add indices to optimize meter deletion --
CREATE NONCLUSTERED INDEX IX_Meter_LocationID
ON Meter(LocationID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CustomerMeter_CustomerID
ON CustomerMeter(CustomerID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CustomerMeter_MeterID
ON CustomerMeter(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AssetChannel_AssetID
ON AssetChannel(AssetID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AssetChannel_ChannelID
ON AssetChannel(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Series_ChannelID
ON Series(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_Series_SeriesTypeID
ON Series(SeriesTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_MaintenanceWindow_MeterID
ON MaintenanceWindow(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerChannel_ChannelID
ON BreakerChannel(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerRestrike_PhaseID
ON BreakerRestrike(PhaseID ASC)
GO


CREATE NONCLUSTERED INDEX IX_BreakerOperation_PhaseID
ON BreakerOperation(PhaseID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerOperation_BreakerOperationTypeID
ON BreakerOperation(BreakerOperationTypeID ASC)
GO

DROP INDEX EventSentEmail.IX_EventSentEmail_EventID
GO

CREATE NONCLUSTERED INDEX IX_EventSentEmail_EventID
ON EventSentEmail(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_EventSentEmail_SentEmailID
ON EventSentEmail(SentEmailID ASC)
GO

CREATE NONCLUSTERED INDEX IX_SnapshotHarmonics_ChannelID
ON SnapshotHarmonics(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_OutputChannel_SeriesID
ON OutputChannel(SeriesID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultSegment_EventID
ON FaultSegment(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultSegment_SegmentTypeID
ON FaultSegment(SegmentTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FaultSummary_LineSegmentID
ON FaultSummary(LineSegmentID ASC)
GO

CREATE NONCLUSTERED INDEX IX_NearestStructure_FaultSummaryID
ON NearestStructure(FaultSummaryID ASC)
GO

CREATE NONCLUSTERED INDEX IX_NearestStructure_StructureID
ON NearestStructure(StructureID ASC)
GO

CREATE NONCLUSTERED INDEX IX_LightningStrike_EventID
ON LightningStrike(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_VaisalaExtendedLightningData_LightningStrikeID
ON VaisalaExtendedLightningData(LightningStrikeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmLog_AlarmID
ON AlarmLog(AlarmID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmLog_AlarmFactorID
ON AlarmLog(AlarmFactorID ASC)
GO

CREATE NONCLUSTERED INDEX IX_AlarmLog_SeverityID
ON AlarmLog(SeverityID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupLocalToRemote_RemoteXDAInstanceID
ON FileGroupLocalToRemote(RemoteXDAInstanceID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupLocalToRemote_LocalFileGroupID
ON FileGroupLocalToRemote(LocalFileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupLocalToRemote_RemoteFileGroupID
ON FileGroupLocalToRemote(RemoteFileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_PQMarkCompanyMeter_CompanyID
ON CompanyMeter(CompanyID) 
GO

CREATE NONCLUSTERED INDEX IX_Report_MeterID
ON Report(MeterID ASC)
GO

CREATE NONCLUSTERED INDEX IX_EventStat_EventID
ON EventStat(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_RelayPerformance_EventID
ON RelayPerformance(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_RelayPerformance_ChannelID
ON RelayPerformance(ChannelID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBAnalyticResult_EventID
ON CBAnalyticResult(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBAnalyticResult_PhaseID
ON CBAnalyticResult(PhaseID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBRestrikeResult_CBResultID
ON CBRestrikeResult(CBResultID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBRestrikeResult_CBRestrikeTypeID
ON CBRestrikeResult(CBRestrikeTypeID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBSwitchHealthAnalytic_CBResultID
ON CBSwitchHealthAnalytic(CBResultID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBSwitchHealthAnalytic_CBSwitchingConditionID
ON CBSwitchHealthAnalytic(CBSwitchingConditionID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBCapBankResult_CBResultID
ON CBCapBankResult(CBResultID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBCapBankResult_CBBankHealthID
ON CBCapBankResult(CBBankHealthID ASC)
GO

CREATE NONCLUSTERED INDEX IX_CBCapBankResult_CBOperationID
ON CBCapBankResult(CBOperationID ASC)
GO


-- Add columns to ScheduledEmailType for Model update --
ALTER TABLE ScheduledEmailType ADD
	RequireApproval BIT NOT NULL DEFAULT 0,
    ShowSubscription BIT NOT NULL DEFAULT 1
GO

-- Add RabbitMQ Node --
INSERT INTO NodeType VALUES('RabbitMQ', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.RabbitMQ.RabbitMQNode')
GO

INSERT INTO Node VALUES((SELECT ID FROM NodeType WHERE TypeName = 'openXDA.Nodes.Types.RabbitMQ.RabbitMQNode'), NULL, NULL, 'RabbitMQ Listener', 1)
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('RabbitMQ.Enabled', 'false', 'false')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('RabbitMQ.ExchangeName', 'openxda', 'openxda')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('RabbitMQ.RoutingKey', 'openxda', 'openxda')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('RabbitMQ.Hostname', 'localhost', 'localhost')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('RabbitMQ.Port', '5672', '5672')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('RabbitMQ.OutboundRoutingKey', 'analytic', 'analytic')
GO

-- PQ Browser Updates --
CREATE VIEW [EventWidgets.EventView] AS
	SELECT
		Event.ID, 
		Event.StartTime,
		Event.EndTime,
		Meter.ID as MeterID, 
		Meter.Name as MeterName, 
		EventType.ID as EventTypeID,
		EventType.Name as EventType,
		distTbl.PerUnitMagnitude,
		distTbl.DurationSeconds,
		distTbl.Phase
	FROM
		Event JOIN
		Meter ON Meter.ID = Event.MeterID JOIN 
		EventType ON Event.EventTypeID = EventType.ID JOIN
		(
			Select Disturbance.DurationSeconds, Disturbance.PerUnitMagnitude, Phase.Name as Phase, Disturbance.EventID
			From Disturbance JOIN 
			EventWorstDisturbance ON EventWorstDisturbance.WorstDisturbanceID = Disturbance.ID JOIN
			Phase ON Phase.ID = Disturbance.PhaseID
			Where Disturbance.Magnitude in (Select Max(Disturbance.Magnitude) From Disturbance Group By Disturbance.EventID)
		) as distTbl ON distTbl.EventID = Event.ID
GO

CREATE VIEW [EventWidgets.EventEventTagView] AS
	SELECT
		EventEventTag.ID,
		EventEventTag.EventID,
		EventEventTag.TagData,
		EventEventTag.EventTagID,
		EventTag.Name as TagName
	FROM 
		EventEventTag LEFT JOIN 
		EventTag ON EventEventTag.EventTagID = EventTag.ID
GO

-- PQ Digest --
CREATE TABLE [PQDigest.Setting](
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name VARCHAR(200) NOT NULL UNIQUE,
    Value VARCHAR(MAX) NULL,
    DefaultValue VARCHAR(MAX) NULL
)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

INSERT INTO [PQDigest.Setting](Name, Value, DefaultValue) VALUES('XDA.Url', 'http://localhost:8989', '')
GO
INSERT INTO [PQDigest.Setting](Name, Value, DefaultValue) VALUES('XDA.APIKey', '', '')
GO
INSERT INTO [PQDigest.Setting](Name, Value, DefaultValue) VALUES('XDA.APIToken', '', '')
GO

CREATE TABLE [PQDigest.EventViewWidget] (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL DEFAULT(1),
    Setting varchar(max) NOT NULL Default('{}'),
    Type varchar(max) NOT NULL Default('OpenSEE')
)
GO

INSERT [PQDigest.EventViewWidget] (Name, Type) VALUES ('OpenSEE','OpenSEE')
GO

INSERT [PQDigest.EventViewWidget] (Name, Type) VALUES ('TrendGraph','TrendGraph')
GO

INSERT [PQDigest.EventViewWidget] (Name, Type) VALUES ('PQICurves','PQICurves')
GO

INSERT [PQDigest.EventViewWidget] (Name, Type) VALUES ('pqi','pqi')
GO


CREATE TABLE [PQDigest.HomeScreenWidget] (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL DEFAULT(1),
    Setting varchar(max) NOT NULL Default('{}'),
    Type varchar(max) NOT NULL Default('OpenSEE'),
    TimeFrame INT NOT NULL Default(30)
)

INSERT [PQDigest.HomeScreenWidget] (Name, Type, TimeFrame) VALUES ('EPRI PQ Health Index - Last 30 Days','PQHealthIndex', 30)
GO

INSERT [PQDigest.HomeScreenWidget] (Name, Type, TimeFrame) VALUES ('Historical Event Counts - Last 30 Days','EventCountChart', 30)
GO

INSERT [PQDigest.HomeScreenWidget] (Name, Type, TimeFrame) VALUES ('Magnitude Duration Chart - Last 30 Days','MagDurChart', 30)
GO

INSERT [PQDigest.HomeScreenWidget] (Name, Type, TimeFrame) VALUES ('Meter Activity - Last 30 Days','EventCountTable', 30)
GO